﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Web.UI;
using System.Web;
using GoShopping.Model;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Threading;


namespace GoShopping.BLL.PrdctInfo
{
    /// <summary>
    /// 操作Excel
    /// </summary>
    public class ExcelHelper
    {
        System.Reflection.Missing miss = System.Reflection.Missing.Value;
        private ApplicationClass _xlApp;
        private Workbook _xlBook;
        private Worksheet _xlSheet;
        private Range _xlsRange;

        /// <summary>
        /// New Function
        /// </summary>
        public ExcelHelper()
        {

        }

        /// <summary>
        /// 清楚Excel目录
        /// 需要定期使用以清除不用的Excel
        /// </summary>
        public void ClearExcelPath(Page page)
        {
            if (Directory.Exists(page.Server.MapPath("~/Excel")))
            {
                Directory.Delete(page.Server.MapPath("~/Excel"), true);
                Directory.CreateDirectory(page.Server.MapPath("~/Excel"));
            }
        }

        /// <summary>
        /// 清除以前不用的Excel文件
        /// 保留时间范围1天
        /// </summary>
        public void ClearAgoExcelFiles(Page page)
        {
            if (Directory.Exists(page.Server.MapPath("~/Excel")))
            {
                string[] files = Directory.GetFiles(page.Server.MapPath("~/Excel"));
                FileInfo fi;
                foreach (string s in files)
                {
                    fi = new FileInfo(s);
                    TimeSpan ts = DateTime.Now -fi.CreationTime;
                    if (ts.Days >= 1)
                    {
                        fi.Delete();
                    }
                }
            }
        }

        /// <summary>
        /// 初始化Excel Application
        /// </summary>
        private void InitExcelApp()
        {
            try
            {
                _xlApp = new ApplicationClass();
                _xlApp.Visible = false;
                Workbooks _objBooks = (Workbooks)_xlApp.Workbooks;
                _xlBook = _objBooks.Add(miss);
                _xlSheet = (Worksheet)_xlBook.ActiveSheet;

                if (_xlApp.Sheets.Count > 1)
                {
                    int num = _xlApp.Sheets.Count;
                    for (int i = 2; i < num + 1; i++)
                    {
                        ((Worksheet)_xlApp.Sheets["Sheet" + i.ToString()]).Delete();
                    }
                }
            }
            catch(Exception ex)
            {
                throw ex;//new Exception("没有正确安装Excel");
            }
        }

        /// <summary>
        /// Dispose
        /// </summary>
        private void ReleaseExcel()
        {
            if (_xlApp != null)
            {
                _xlBook.Close(false, miss, miss);
                Thread.Sleep(1000);
                _xlApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(_xlBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(_xlSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(_xlApp);


                _xlApp = null;
                _xlBook = null;
                _xlSheet = null;
                GC.Collect();
            }
        }

        private void FormatBackColor(Range rg)
        {
            rg.Interior.ColorIndex = 34;
        }

        /// <summary>
        /// 设置某列为数字格式
        /// </summary>
        /// <param name="columnIndex"></param>
        private void SetColumnNumber(int columnIndex)
        {
            Range rg;
            rg = _xlSheet.get_Range(_xlSheet.Cells[1, columnIndex], _xlSheet.Cells[2000, columnIndex]);
            rg.NumberFormatLocal = "0_ ";
        }

        /// <summary>
        /// 设置某列为日期格式
        /// </summary>
        /// <param name="columnIndex"></param>
        private void SetColumnDate(int columnIndex)
        {
            Range rg;
            rg = _xlSheet.get_Range(_xlSheet.Cells[1, columnIndex], _xlSheet.Cells[2000, columnIndex]);
            rg.NumberFormatLocal = "yyyy-m-d";
        }
        /// <summary>
        /// 设置某列为会计格式
        /// </summary>
        /// <param name="columnIndex"></param>
        private void SetColumnCush(int columnIndex)
        {
            Range rg;
            rg = _xlSheet.get_Range(_xlSheet.Cells[1, columnIndex], _xlSheet.Cells[2000, columnIndex]);
            rg.NumberFormatLocal = @"_ ￥* #,##0.00_ ;_ ￥* -#,##0.00_ ;_ ￥* ""-""??_ ;_ @_ ";
        }

        /// <summary>
        /// 设置该范围的值从下拉列表中选取
        /// </summary>
        /// <param name="rg"></param>
        /// <param name="list"></param>
        private void FormatRangeValueList(Range rg, string list)
        {
            if (!string.IsNullOrEmpty(list))
            {
                rg.Validation.Add(XlDVType.xlValidateList,
                    XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, list, "");
                rg.Validation.InCellDropdown = true;


                rg.Validation.ErrorTitle = "警告";
                rg.Validation.InputMessage = "请从列表选择一条记录";
                rg.Validation.ErrorMessage = "你输入的内容并不符合要求，请从列表选择一条记录";
                rg.Validation.ShowInput = true;
                rg.Validation.ShowError = true;
            }
        }

        /// <summary>
        /// 导入进货文件
        /// </summary>
        /// <param name="fu"></param>
        /// <returns></returns>
        public IList<StockProduct> ImportResult(Page page, FileUpload fu)
        {
            try
            {
                ClearAgoExcelFiles(page);

                if (!Directory.Exists(page.Server.MapPath("~/Excel")))
                {
                    Directory.CreateDirectory(page.Server.MapPath("~/Excel"));
                }

                List<StockProduct> list = new List<StockProduct>();
                if (!fu.HasFile || fu.PostedFile.FileName == "")
                {
                    return list;
                }
                string[] tm = fu.FileName.Split('.');
                string newFileName = DateTime.Now.Ticks.ToString() + "." + tm[1];
                string filePath = page.Server.MapPath("~/Excel") + "\\" + newFileName;
                fu.PostedFile.SaveAs(filePath);
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + "Extended Properties=\"Excel 8.0;IMEX=1;\"";//连接
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                string tableName = schemaTable.Rows[0][2].ToString().Trim();//得到excel表名
                OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + tableName + "]", strConn);//查找excel数据
                DataSet myDataSet = new DataSet();//定义数据
                myCommand.Fill(myDataSet);
                conn.Close();

                ProductBean pb = new ProductBean();
                foreach (DataRow dr in myDataSet.Tables[0].Rows)
                {
                    StockProduct sp = new StockProduct();
                    if (dr["序号"] == DBNull.Value && dr["商品名称"] == DBNull.Value)
                    {
                        continue;
                    }
                    sp.ID = int.Parse(dr["序号"].ToString());
                    sp.Durability = int.Parse(dr["保质期（天）"].ToString());
                    sp.InCount = int.Parse(dr["进货数量(个)"].ToString());
                    sp.InDate = DateTime.Parse(dr["进货日期"].ToString());
                    sp.InPrice = Decimal.Parse(dr["进货价"].ToString());
                    sp.MarketPrice = Decimal.Parse(dr["当前市场价"].ToString());
                    sp.ProduceDate = DateTime.Parse(dr["生产日期"].ToString());
                    //sp.ProductID = Decimal.Parse(dr["商品名称"].ToString());
                    sp.SalePrice = Decimal.Parse(dr["出售价格"].ToString());

                    Product p = pb.GetProduct(dr["商品名称"].ToString());
                    if (p != null)
                    {
                        sp.ProductID = p.ID;
                        list.Add(sp);
                    }
                }
                return list;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 导出模板
        /// </summary>
        public void ExportTemplate(Page page)
        {

            ClearAgoExcelFiles(page);

            string strFileName;
            try
            {
                #region 生成准备
                if (!Directory.Exists(page.Server.MapPath("~/Excel")))
                {
                    Directory.CreateDirectory(page.Server.MapPath("~/Excel"));
                }

                strFileName = page.Server.MapPath("~") + @"\Excel\" + DateTime.Now.Ticks.ToString() + ".xls";
                if (File.Exists(strFileName))
                {
                    File.SetAttributes(strFileName, FileAttributes.Normal);
                    try
                    {
                        File.Delete(strFileName);
                    }
                    catch (Exception ex)
                    {
                        page.Response.Write(ex.Message);
                        page.Response.Write("<script language=javascript>alter('删除临时文件失败')</script>");
                        return;
                    }

                }
                #endregion

                #region 初始化Excel
                InitExcelApp();
                #endregion

                //开始生成数据
                System.Data.DataTable dtEmpty = Databases.DBGoShopping.Select(
                    GoShoppingNBear.StockProduct).Where(GoShoppingNBear.StockProduct.ID < 0).ToDataSet().Tables[0];
                int i = 1;
                _xlSheet.Cells[1, i] = "序号";

                //i++;
                //_xlSheet.Cells[1, i] = "商品类型";
                foreach (System.Data.DataColumn dc in dtEmpty.Columns)
                {

                    i++;
                    string columnName;
                    if (dc.ColumnName.ToUpper() == "ID")
                    {
                        i--;
                        continue;
                    }
                    columnName = ConvertEnName(dc.ColumnName);
                    _xlSheet.Cells[1, i] = columnName;
                    if (dc.ColumnName.ToLower() == "durability")
                    {
                        this.SetColumnNumber(i);
                    }
                    if (dc.ColumnName.ToLower() == "incount")
                    {
                        this.SetColumnNumber(i);
                    }
                    if (dc.ColumnName.ToLower() == "indate")
                    {
                        this.SetColumnDate(i);
                    }

                    if (dc.ColumnName.ToLower() == "inprice")
                    {
                        this.SetColumnCush(i);
                    }

                    if (dc.ColumnName.ToLower() == "saleprice")
                    {
                        this.SetColumnCush(i);
                    }

                    if (dc.ColumnName.ToLower() == "marketprice")
                    {
                        this.SetColumnCush(i);
                    }
                    //producedate
                    if (dc.ColumnName.ToLower() == "producedate")
                    {
                        this.SetColumnDate(i);
                    }

                    if (dc.ColumnName.ToLower() == "productid")
                    {
                        Range rg1 = _xlSheet.get_Range(_xlSheet.Cells[2, i], _xlSheet.Cells[2000, i]);
                        ProductBean pb = new ProductBean();
                        IList<Product> list = pb.GetAllProduct();
                        StringBuilder db = new StringBuilder();
                        foreach (Product p in list)
                        {
                            db.Append(p.Name + ",");
                        }
                        if (db.Length > 0)
                        {
                            db.Remove(db.Length - 1, 1);
                        }
                        this.FormatRangeValueList(rg1, db.ToString());
                    }
                }

                Range rg = _xlSheet.get_Range(_xlSheet.Cells[1, 1], _xlSheet.Cells[1, i]);
                this.FormatBackColor(rg);
                //最适合列宽
                _xlSheet.Columns.AutoFit();

                #region Excel输出到客户端

                _xlBook.SaveCopyAs(strFileName);
                this.ReleaseExcel();

                System.IO.FileInfo file = new System.IO.FileInfo(strFileName);

                page.Response.Clear();
                page.Response.Charset = "GB2312";
                page.Response.ContentEncoding = System.Text.Encoding.UTF8;
                // 添加头信息，为"文件下载/另存为"对话框指定默认文件名
                page.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(file.Name));
                // 添加头信息，指定文件大小，让浏览器能够显示下载进度
                page.Response.AddHeader("Content-Length", file.Length.ToString());

                // 指定返回的是一个不能被客户端读取的流，必须被下载
                page.Response.ContentType = "application/ms-excel";

                // 把文件流发送到客户端
                page.Response.WriteFile(file.FullName);
                // 停止页面的执行
                page.Response.End();
                #endregion
            }
            catch (Exception ex)
            {
                page.Response.Write(ex.Message);
                page.Response.Write("<script language=javascript>alter('生成模板失败')</script>");
                return;
            }
            finally
            {
                this.ReleaseExcel();
            }


            
        }

        private string ConvertEnName(string enName)
        {
            string rt = string.Empty;
            switch (enName.ToLower())
            {
                case "durability":
                    rt = "保质期（天）";
                    break;
                case "incount":
                    rt = "进货数量(个)";
                    break;
                case "indate":
                    rt = "进货日期";
                    break;
                case "inprice":
                    rt = "进货价";
                    break;
                case "saleprice":
                    rt = "出售价格";
                    break;
                case "marketprice":
                    rt = "当前市场价";
                    break;
                case "producedate":
                    rt = "生产日期";
                    break;
                case "productid":
                    rt = "商品名称";
                    break;
                default:
                    rt = enName;
                    break;
            }
            return rt;
        }

    }
}
